import 'package:flutter_first_station/muyu/models/merit_record.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart' as path;

/// sqlite 数据库
class DBStorage {
  DBStorage._();

  static DBStorage? _storage;

  static DBStorage get instance {
    _storage = _storage ?? DBStorage._();
    return _storage!;
  }

  late Database _db;
  late MeritRecordDao _meritRecordDao;
  MeritRecordDao get meritRecordDao => _meritRecordDao;

  Future<void> open() async {
    String databasePath = await getDatabasesPath();
    String dbPath = path.join(databasePath, 'first_station.db');
    _db = await openDatabase(dbPath, version: 1, onCreate: _onCreate);
    _meritRecordDao = MeritRecordDao(_db);
  }

  void _onCreate(Database db, int version) async {
    await MeritRecordDao.createTable(db);
  }
}

class MeritRecordDao {
  final Database database;

  MeritRecordDao(this.database);

  static String tableName = 'merit_record';
  static String tableSql =
      """ CREATE TABLE $tableName (id VARCHAR(64) PRIMARY KEY, value INTEGER, image TEXT, audio TEXT, timestamp INTEGER) """;

  // 创建数据库
  static Future<void> createTable(Database db) async {
    return db.execute(tableSql);
  }

  // 插入数据
  Future<int> insert(MeritRecord record) {
    return database.insert(tableName, record.toJson(),
        conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // 查询数据
  Future<List<MeritRecord>> query() async {
    List<Map<String, Object?>> data = await database.query(tableName);
    return data
        .map((e) => MeritRecord(
              e['id'].toString(),
              e['timestamp'] as int,
              e['value'] as int,
              e['image'].toString(),
              e['audio'].toString(),
            ))
        .toList();
  }
}
